﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DbGenerator.Model.units.table;
using Unity.Errors.types;
using DbGenerator.Model.units.column;

namespace DbExecuter.DataLayer.commands
{
    public class TInsertCommand : TCommandBase
    {
        #region ctors
        public TInsertCommand(SqlConnection connection)
            : base(connection)
        {

        }
        #endregion

        #region public methods
        public int Insert(TTable insertTable)
        {
            TColumn[] insertColumns = insertTable.Columns.ToArray();
            TCommandParameter[] insertParameters = GetInsertParameters(insertColumns);
            TInsertClause insertClause = 
                new TInsertClause(insertColumns, insertParameters);

            string cmdText
                = String.Format("\nUSE {0}\n{1}"
                , insertTable.BaseCatalog.DatabaseName
                , insertClause.ToString());

            //Add paramaters:
            int valueParameterCount = insertParameters.Length;
            for (int i = 0; i < valueParameterCount; i++)
                CommandParameters.Add(insertParameters[i]);

            //Construct Command:
            ConstructCommand(cmdText);
            OpenConnection();
            int r = Command.ExecuteNonQuery();
            CloseConnection();
            return r;
        }
        public int InsertIdentity(TTable insertTable)
        {
            TColumn[] insertColumns = insertTable.Columns.ToArray();
            TCommandParameter[] insertParameters = GetInsertParameters(insertColumns);
            TInsertClause insertClause =
                new TInsertClause(insertColumns, insertParameters);

            string cmdText
                = String.Format("\nUSE {0}\nINSERT INTO {1}"
                , insertTable.BaseCatalog.DatabaseName
                , insertClause.ToString());
            string identityCmdText = "\nSELECT SCOPE_IDENTITY();";
            //Add paramaters:
            int valueParameterCount = insertParameters.Length;
            for (int i = 0; i < valueParameterCount; i++)
                CommandParameters.Add(insertParameters[i]);

            //Construct insert Command:
            ConstructCommand(cmdText);

            //Open connection for two commands
            OpenConnection();

            int r = 0;
            //execute insert command:
            try
            {
                r = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SErrorManager.Errors("CommandExecutionFailed")
                    .Throw(ex, new TErrorInfo(GetType(), "InsertIdentity")
                    , Command.CommandText);
                CloseConnection();
            }
            //now construct identity command and execute it:
            ConstructCommand(identityCmdText);
            try
            {
                r = Convert.ToInt32(Command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                SErrorManager.Errors("CommandExecutionFailed")
                    .Throw(ex, new TErrorInfo(GetType(), "InsertIdentity")
                    , Command.CommandText);
                CloseConnection();
            }
            CloseConnection();
            return r;
        }
        public int Insert(TTable intoTable, TInsertClause insertClause)
        {
            string cmdText
                = String.Format("\nUSE {0}\nINSERT INTO {1}"
                , intoTable.BaseCatalog.DatabaseName
                , insertClause.ToString());

            //Add paramaters:
            int valueParameterCount = insertClause.InsertParameters.Length;
            for (int i = 0; i < valueParameterCount; i++)
                CommandParameters.Add(insertClause.InsertParameters[i]);

            //Construct Command:
            ConstructCommand(cmdText);
            OpenConnection();
            int r = Command.ExecuteNonQuery();
            CloseConnection();
            return r;
        }
        public int InsertIdentity(TTable intoTable, TInsertClause insertClause)
        {
            string cmdText
                = String.Format("\nUSE {0}\nINSERT INTO {1}"
                , intoTable.BaseCatalog.DatabaseName
                , insertClause.ToString());
            string identityCmdText = "\nSELECT SCOPE_IDENTITY();";
            //Add paramaters:
            int valueParameterCount = insertClause.InsertParameters.Length;
            for (int i = 0; i < valueParameterCount; i++)
                CommandParameters.Add(insertClause.InsertParameters[i]);

            //Construct Command:
            ConstructCommand(cmdText);

            //Open connection for two commands
            OpenConnection();

            int r = 0;
            //execute insert command:
            try
            {
                r = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SErrorManager.Errors("CommandExecutionFailed")
                    .Throw(ex, new TErrorInfo(GetType(), "InsertIdentity")
                    , Command.CommandText);
                CloseConnection();
            }
            //now construct identity command and execute it:
            ConstructCommand(identityCmdText);
            try
            {
                r = Convert.ToInt32(Command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                SErrorManager.Errors("CommandExecutionFailed")
                    .Throw(ex, new TErrorInfo(GetType(), "InsertIdentity")
                    , Command.CommandText);
                CloseConnection();
            }
            CloseConnection();
            return r;
        }
        #endregion

        #region private methods
        private TCommandParameter[] GetInsertParameters(TColumn[] insertColumns)
        {
            int columnCount = insertColumns.Length;
            TCommandParameter[] insertParameters = new TCommandParameter[columnCount];
            for (int i = 0; i < columnCount; i++)
            {
                insertParameters[i] = new TCommandParameter(insertColumns[i]);
            }
            return insertParameters;
        }
        #endregion
    }
}
